﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using DBUtility;
using Model;
using System.Data;
using CommonUtility;
using System.Collections;

namespace DAL
{
    public class DalExtend
    {
        /// <summary>
        /// 
    }
    public class SQLCommon
    {
        /// <summary>
        /// 返回一个事务
        /// </summary>
        /// <returns></returns>
        public static SqlTransaction GetSqlTransaction()
        {
            System.Data.SqlClient.SqlConnection conn = SqlHelper.CreateConnection();
            conn.Open();
            System.Data.SqlClient.SqlTransaction myTran = conn.BeginTransaction();
            return myTran;
        }
    }


    public partial class User
    {
        /// <summary>
        /// 获取角色下的所有用户
        /// </summary>
        /// <param name="flowNode"></param>
        /// <returns></returns>
        public static IList<UserInfo> GetRoleUsers(string _roleID)
        {
            IList<UserInfo> list = new List<UserInfo>();
            try
            {
                string strSQL = @"
SELECT 
	[RID],
	[userID],
	[name],
	[email],
	[eightID],
	[pwd],
	[flag],
	[lpt],
	[whom]
FROM [vRoleUser]  WHERE [roleID]='" + _roleID + "'";

                DataSet mySet = new DataSet();
                SqlHelper.FillDataset(strSQL, mySet, "data", true);
                if (mySet != null && mySet.Tables.Count > 0)
                {
                    foreach (DataRow dr in mySet.Tables["data"].Rows)
                    {
                        UserInfo model = new UserInfo();
                        model.RID = Common.ToInt32(dr["RID"]);
                        model.UserID = Common.ToString(dr["userID"]);
                        model.Name = Common.ToString(dr["name"]);
                        model.Email = Common.ToString(dr["email"]);
                        model.EightID = Common.ToString(dr["eightID"]);
                        model.Pwd = Common.ToString(dr["pwd"]);
                        model.Flag = Common.ToString(dr["flag"]);
                        model.Lpt = Common.ToString(dr["lpt"]);
                        model.Whom = Common.ToString(dr["whom"]);
                        list.Add(model);
                    }
                }
                mySet.Dispose();
            }
            catch (Exception ex)
            {
                Common.Application_Error_Server("User.cs/GetRoleUsers(" + _roleID + ")", ex);
            }
            return list;
        }


    }



    public partial class Action
    {
        /// <summary>
        /// 获取描述
        /// </summary>
        /// <param name="actID"></param>
        /// <returns></returns>
        public static string GetActDesc(string actID)
        {
            string res = string.Empty;
            try
            {
                IList<ActionInfo> list = GetList(" actID = '" + actID + "'");
                if (list.Count > 0)
                {
                    res = string.Join(",", list.Select(d => d.ActName).ToArray());
                }
            }
            catch (Exception ex)
            {
                Common.Application_Error_Server("DalExtend.cs(Action)/GetActDesc(" + actID + ")", ex);
            }
            return res;
        }
    }


    public partial class FunctionAction
    {
        /// <summary>
        /// 根据方法获取ActDesc
        /// </summary>
        /// <param name="funID"></param>
        /// <returns></returns>
        public static string GetActDesc(string funID)
        {
            IList<FunctionActionInfo> list = new List<FunctionActionInfo>();
            string res = "";
            try
            {
                string strSQL = @"
select funact.RID,funact.funID,funact.actID from dbo.tFunctionAction funact
join tAction act on act.actID=funact.actID and act.flag='A'";
                if (!string.IsNullOrEmpty(funID))
                    strSQL += " WHERE [funID]='" + funID + "'";

                using (SqlDataReader sdr = SqlHelper.ExecuteReader(strSQL))
                {
                    while (sdr.Read())
                    {
                        res += Action.GetActDesc(Common.ToString(sdr["actID"])) + "/";
                    }
                    sdr.Close();

                }
            }
            catch (Exception ex)
            {
                Common.Application_Error_Server("DalExtend.cs(FuntionAction)/GetActDesc(" + funID + ")", ex);
            }
            return res.Trim('/');

        }

        /// <summary>
        /// 根据方法获取ActID
        /// </summary>
        /// <param name="funID"></param>
        /// <returns></returns>
        public static string GetActID(string funID)
        {
            IList<FunctionActionInfo> list = new List<FunctionActionInfo>();
            string res = "";
            try
            {
                string strSQL = @"
select funact.RID,funact.funID,funact.actID from dbo.tFunctionAction funact
join tAction act on act.actID=funact.actID and act.flag='A'";
                if (!string.IsNullOrEmpty(funID))
                    strSQL += " WHERE [funID]='" + funID + "'";

                using (SqlDataReader sdr = SqlHelper.ExecuteReader(strSQL))
                {
                    while (sdr.Read())
                    {
                        res += Common.ToString(sdr["ActID"]) + "/";
                    }
                    sdr.Close();
                }
            }
            catch (Exception ex)
            {
                Common.Application_Error_Server("DalExtend.cs(FuntionAction)/GetActID(" + funID + ")", ex);
            }
            return res.Trim('/');

        }

        public static bool Delete(string funID)
        {
            bool res = true;
            try
            {
                string strSQL = "DELETE [tFunctionAction] WHERE  [funID]=@funID;";
                SqlParameter[] para = new SqlParameter[1];
                para[0] = new SqlParameter("@funID", funID);
                int _num = Common.ToInt32(SqlHelper.ExecuteNonQuery(strSQL, para));
                if (_num == 0)
                    res = false;
            }
            catch (Exception ex)
            {
                res = false;
                Common.Application_Error_Server("DalExtend.cs(FunctionAction)/Delete()", ex);
                throw ex;
            }
            return res;
        }

        /// <summary>
        /// 获取功能最多方法个数
        /// </summary>
        /// <returns></returns>
        public static int GetMaxFunLen()
        {
            int result = 0;
            try
            {
                result = Common.ToInt32(SqlHelper.ExecuteScalar("SELECT MAX(cnt) FROM (SELECT COUNT(complex.FunID) AS cnt FROM (SELECT funact.FunID FROM dbo.tFunctionAction funact JOIN dbo.tAction act ON act.ActID = funact.ActID AND act.flag = 'A') AS complex GROUP BY complex.FunID) AS a"));
            }
            catch (Exception ex)
            {
                Common.Application_Error_Server("DalExtend.cs(FunctionAction)/GetMaxFunLen()", ex);
            }
            return result;
        }
    }

    public partial class RolePermission
    {
        public static IList<RoleFunctionActionInlist> GetRoleFunctionActionInlist(string roleID)
        {
            IList<RoleFunctionActionInlist> list = new List<RoleFunctionActionInlist>();
            try
            {
                string strSQL = @"SELECT roleID,funID, actioinLst=STUFF((SELECT '/'+actID FROM tRolePermission t WHERE roleID=t1.roleID and funID=t1.funID FOR XML PATH('')), 1, 1, '')
FROM tRolePermission t1 where t1.roleID='" + roleID + "' and t1.flag='A' GROUP BY roleID,funID";

                DataSet mySet = new DataSet();
                SqlHelper.FillDataset(strSQL, mySet, "data", true);
                if (mySet != null && mySet.Tables.Count > 0)
                {
                    foreach (DataRow dr in mySet.Tables["data"].Rows)
                    {
                        RoleFunctionActionInlist model = new RoleFunctionActionInlist();
                        model.FunID = Common.ToString(dr["funID"]);
                        model.RoleID = Common.ToString(dr["roleID"]);
                        model.ActioinLst = Common.ToString(dr["actioinLst"]);
                        list.Add(model);
                    }
                }
                mySet.Dispose();
            }
            catch (Exception ex)
            {
                Common.Application_Error_Server("RolePermission.cs/GetRoleFunctionActionInlist(" + roleID + ")", ex);
            }
            return list;
        }
    }



    public partial class SystemCommon
    {
        /// <summary>
        /// 获取分页后的Table
        /// </summary>
        /// <param name="_tableName">表名</param>
        /// <param name="_pageIndex">页码</param>
        /// <param name="_pageSize">页大小</param>
        /// <param name="_condition">条件</param>
        /// <param name="_orderStr">排序</param>
        /// <returns></returns>
        public static DataSet GetPage(string _tableName, int _pageIndex, int _pageSize, string _condition, string _orderStr)
        {
            if (string.IsNullOrEmpty(_orderStr))
                _orderStr = "RID DESC";
            string strSQL = "Select Top " + _pageSize.ToString().Trim() + " * From " + _tableName +
                         " Where [RID] Not in (SELECT TOP " +
                           (_pageSize * (_pageIndex - 1)).ToString() + " [RID] From " + _tableName +
                           ((_condition == "") ? "" : (" Where " + _condition)) +
                           ((_orderStr == "") ? "" : (" Order By ") + _orderStr) + ")" +
                         ((_condition == "") ? "" : (" and " + _condition)) +
                         ((_orderStr == "") ? "" : (" Order By ") + _orderStr);
            DataSet mySet = new DataSet();
            SqlHelper.FillDataset(strSQL, mySet, "data", true);
            return mySet;
        }

        /// <summary>
        /// 获取记录总数
        /// </summary>
        /// <param name="_tableName"></param>
        /// <returns></returns>
        public static int GetPageCount(string _tableName, string _where)
        {

            string strSQL = "select count(*) from " + _tableName + " where " + _where;
            return Common.ToInt32(SqlHelper.ExecuteScalar(strSQL).ToString());
        }
    }

}
